Two possible approaches for backing up and restoring a database include the "logical" approach and the "physical" approach. According to the logical approach, the backup of a database involves reading the data to be backed up from the database, converting the data from its native database representation to a user/application layer format, such as columns and rows, and then storing the user data in a backup file. To restore the data, the user data is read from the backup file, converted back into its native database format and written to disk. SQL (Structured Query Language) retrieve and insert calls are sometimes used to convert the data from native disk format to user format and from user format back to native database format.
By converting the database data from native database representation to a higher level user format, the logical approach supports the backup and restore of data between different types of databases, as well as bulk data transfer between databases. Converting the data into user format also provides the benefit of only backing up the actual data, without the internal database data structures associated with the data, thereby minimizing the amount of data which needs to be backed up and restored. However, despite the inherent flexibility offered by the logical approach, the physical-to-logical conversion of data during both the backup and restore can require large amounts of time and system resources.
In contrast to the logical approach, backing up a database according to the physical approach involves copying database data files from a database to a backup location without converting the data contained in the data files. The data is restored by copying the data files from the backup location back to the database. Sometimes an operating system call or other copy utility is used to copy the data files, making the physical approach much faster than the logical approach.
However, the physical approach also has its disadvantages. First, some database systems automatically allocate a minimum amount of data space regardless of whether all of the allocated space is used. Thus, some space that is physically used is logically unused. Since all of the allocated space is located in the system data files, backing up and restoring all of the data files for a database necessarily means that logically unused data blocks will be backed up and restored, wasting system resources and increasing the backup and restore time.
Perhaps the most important limitation associated with the physical approach is that data files may only be restored to a database system supporting the database file format of the original database. Moreover, even if files are to be restored to a compatible database, the data cannot be directly inserted into the database if that database already contains data.
In view of the advantages and disadvantages provided by the logical and physical approaches, an approach providing for the backup and restore of a database and the transfer of bulk data having the flexibility of the logical approach and the favorable performance characteristics of the physical approach is highly desirable.